﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using System.Data.Common;
using System.Data;
using NpgsqlTypes;
using DbSharp.Utils.Db;
using System.Globalization;
using DbSharp.Framework.DbObject;
using DbSharp.Framework.Common;
using System.ComponentModel;
using DbSharp.Framework.Driver.FieldPersistence;


namespace DbSharp.Framework.Driver
{

    /// <summary>
    /// http://npgsql.projects.postgresql.org/docs/manual/UserManual.html
    /// </summary>
    public class PostgresqlTestPlugin : ITestDataSource
    {
 

        #region ITestDataSource Members

        public DateTime GetDbServerTime(DbConnection conn)
        {
            string sql = "select localtimestamp; ";
            DbCommand cmd = new  NpgsqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;

            DateTime result = (DateTime)cmd.ExecuteScalar();;
            return result;
        }

        public bool IsRecordsetTypeArgument(string argumentTypeText)
        {
            if (GetArgumentType(argumentTypeText) == (int)NpgsqlDbType.Refcursor)
                return true;
            else
                return false;
        }

        private string EndWithSemiColons(string sql)
        {
            return SqlTextHelper.EndWithCharacter(sql, ';');
        }



        private int GetArgumentType(string argumentTypeText)
        {
            NpgsqlDbType parameterType;
            string upperTypeStr = argumentTypeText.Trim().ToUpper();
            switch (upperTypeStr)
            {
                /*
    Array = -2147483648,
  x  Bigint = 1,
   X Boolean = 2,
    Box = 3,
    Bytea = 4,
    Circle = 5,
   X Char = 6,
   X Date = 7,
   X Double = 8,
   X Integer = 9,
    Line = 10,
    LSeg = 11,
  X  Money = 12,
  x  Numeric = 13,
    Path = 14,
    Point = 15,
    Polygon = 16,
 x   Real = 17,
 x   Smallint = 18,
  x  Text = 19,
 x   Time = 20,
 x   Timestamp = 21,
  X  Varchar = 22,
 x   Refcursor = 23,
  x  Inet = 24,
  x  Bit = 25,
  x  TimestampTZ = 26,
  x  Uuid = 27,
    Xml = 28,
    Oidvector = 29,
 x   Interval = 30,
  x  TimeTZ = 31,
 x   Name = 32,
                 */
                case "PGSQL_BIGINT":
                    parameterType = NpgsqlDbType.Bigint;
                    break;
                case "PGSQL_BOOLEAN":
                    parameterType = NpgsqlDbType.Boolean;
                    break;
                case "PGSQL_CHAR":
                    parameterType = NpgsqlDbType.Char;
                    break;
                case "PGSQL_DATE":
                    parameterType = NpgsqlDbType.Date;
                    break;
                case "PGSQL_DOUBLE":
                    parameterType = NpgsqlDbType.Double;
                    break;
                case "PGSQL_INTEGER":
                    parameterType = NpgsqlDbType.Integer;
                    break;
                case "PGSQL_MONEY":
                    parameterType = NpgsqlDbType.Money;
                    break;
                case "PGSQL_NUMERIC":
                    parameterType = NpgsqlDbType.Numeric;
                    break;
                case "PGSQL_REAL":
                    parameterType = NpgsqlDbType.Real;
                    break;
                case "PGSQL_SMALLINT":
                    parameterType = NpgsqlDbType.Smallint;
                    break;
                case "PGSQL_TEXT":
                    parameterType = NpgsqlDbType.Text;
                    break;
                case "PGSQL_TIME":
                    parameterType = NpgsqlDbType.Time;
                    break;
                case "PGSQL_TIMESTAMP":
                    parameterType = NpgsqlDbType.Timestamp;
                    break;
                case "PGSQL_VARCHAR":
                    parameterType = NpgsqlDbType.Varchar;
                    break;
                case "PGSQL_INET":
                    parameterType = NpgsqlDbType.Inet;
                    break;
                case "PGSQL_REFCURSOR":
                    parameterType = NpgsqlDbType.Refcursor;
                    break;
                case "PGSQL_BIT":
                    parameterType = NpgsqlDbType.Bit;
                    break;
                case "PGSQL_TIMESTAMPTZ":
                    parameterType = NpgsqlDbType.TimestampTZ;
                    break;
                case "PGSQL_UUID":
                    parameterType = NpgsqlDbType.Uuid;
                    break;
                case "PGSQL_INTERVAL":
                    parameterType = NpgsqlDbType.Interval;
                    break;
                case "PGSQL_TIMETZ":
                    parameterType = NpgsqlDbType.TimeTZ;
                    break;
                case "PGSQL_NAME":
                    parameterType = NpgsqlDbType.Name;
                    break;
                default:
                    parameterType = NpgsqlDbType.Varchar;
                    break;
            }
            return (int)parameterType;
        }


        public DbConnection CreateConnection(string connString)
        {
            var cnn = new NpgsqlConnection();
            cnn.ConnectionString = connString;
            return cnn;
        }


        public bool IsRecordsetTypeParameter(DbParameter param)
        {
            if (param == null)
                return false;
            else if (((NpgsqlParameter)param).NpgsqlDbType == NpgsqlDbType.Refcursor)
                return true;
            else
                return false;
        }

        public void UpdateCommandText(DbCommand cmd, string newCmdText)
        {
            cmd.CommandText = newCmdText.Trim();
            //make sure the sql statement does end with semicolon
            if (cmd.CommandType == CommandType.Text)
            {
                cmd.CommandText = EndWithSemiColons(cmd.CommandText);
            }
        }


        public bool TransactionSupported
        {
            get { return true; }
        }

 
        public DbCommand PrepareCommand(IStatementDeclaration statement)
        {
            DbCommand cmd = new NpgsqlCommand();
            cmd.CommandType = statement.CommandType;

            IList<StatementArgument> listArgumentObj = statement.ListArgument;
            foreach (var argumentObj in listArgumentObj)
            {
                if (argumentObj.Direction == ArgumentDirection.ClientSide)
                {
                    //Clientside parameter will not added into ParameterList for ever
                    continue;
                }
                else if (argumentObj.IsSqlStmtResultArg)
                {
                    //It means this argument is virtual argument, not the real argument
                    // So the argument will not be added into ParameterList 
                    continue;
                }
                else
                {
                    //Oracle always use named arguments to hold resultsets, so we need add these return type arguments into parameterlist
                }

                var parameter = new NpgsqlParameter();
                parameter.Direction = ArgumentDirectionHelper.ConvertToParamDirection(argumentObj.Direction);
                parameter.NpgsqlDbType = (NpgsqlDbType)GetArgumentType(argumentObj.TypeStr);
                parameter.Size = argumentObj.Size;
                parameter.ParameterName = NormalizeArgumentName(argumentObj.Name);

                cmd.Parameters.Add(parameter);
            }

            return cmd;
        }



        public void AssignParameterValue(DbParameter param, string value, string dateTimeFormat)
        {
            var parameter = (NpgsqlParameter)param;

            if ((parameter.NpgsqlDbType == NpgsqlDbType.Date) ||
                (parameter.NpgsqlDbType == NpgsqlDbType.Time) ||
                (parameter.NpgsqlDbType == NpgsqlDbType.Timestamp) ||
                (parameter.NpgsqlDbType == NpgsqlDbType.TimestampTZ) ||
                (parameter.NpgsqlDbType == NpgsqlDbType.TimeTZ))
            {
                if (string.IsNullOrEmpty(dateTimeFormat))
                    parameter.Value = DateTimeParser.ParseUtc(value);
                else
                    parameter.Value = DateTimeParser.ParseExactUtc(value, dateTimeFormat);
            }
            else if (parameter.NpgsqlDbType == NpgsqlDbType.Interval)
            {
                parameter.Value = NpgsqlInterval.Parse(value);
            }
            else if (parameter.NpgsqlDbType == NpgsqlDbType.Inet)
            {
                parameter.Value = new NpgsqlInet(value);
            }
            else if (parameter.NpgsqlDbType == NpgsqlDbType.Uuid)
            {
                parameter.Value = new Guid(value);
            }
            else
                parameter.Value = value;
        }
  

        public bool HoldResultsetMustWithNamedArgInSP
        {
            get { return false; }
        }

        public string NormalizeArgumentName(string argumentName)
        {
            string arg;
            arg = SqlTextHelper.LeftTrimCharacter(argumentName, '@');
            arg = SqlTextHelper.BeginWithCharacter(argumentName, ':');
            return arg;
        }



        private IFieldPersistence m_FieldPersistenceService = new PostgresqlFieldPersistence();
        public IFieldPersistence FieldPersistenceService
        {
            get { return m_FieldPersistenceService; }
        }



        #endregion
    }
}
